package au.edu.utas.yu_qiao_group.luckyman.db.draws;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import java.util.ArrayList;

import au.edu.utas.yu_qiao_group.luckyman.db.raffles.RaffleDTO;
import au.edu.utas.yu_qiao_group.luckyman.db.raffles.RaffleDrawDTO;
import au.edu.utas.yu_qiao_group.luckyman.db.raffles.RaffleTable;
import au.edu.utas.yu_qiao_group.luckyman.db.tickets.TicketTable;

public class DrawTable {
  public static final String TABLE_NAME = "draws";
  public static final String TRIGGER_NAME_1 = "draws_update_timestamp_trigger";

  public static final String KEY_DRAW_ID = "id";
  public static final String KEY_RAFFLE_ID = "raffle_id";
  public static final String KEY_DRAW_NO = "draw_no";
  public static final String KEY_STATUS = "status"; // 0: 未开, 1: 已开
  public static final String KEY_DRAW_TIME = "draw_time";
  public static final String KEY_DRAW_METHOD = "draw_method"; // 0: normal, 1: margin
  public static final String KEY_WINNER_PHONE = "winner_phone";
  public static final String KEY_WINNER_TICKET_NO = "winner_ticket_no";
  public static final String KEY_CREATE_TIME = "create_time";
  public static final String KEY_UPDATE_TIME = "update_time";
  public static final String KEY_DEL_FLAG = "del_flg";

  public static final String CREATE_STATEMENT = "CREATE TABLE IF NOT EXISTS " + TABLE_NAME
          + " (" + KEY_DRAW_ID + " integer primary key autoincrement, "
          + KEY_RAFFLE_ID + " integer not null, "
          + KEY_DRAW_NO + " char(20) not null, "
          + KEY_STATUS + " tinyint not null, "
          + KEY_DRAW_TIME + " datetime not null, "
          + KEY_DRAW_METHOD + " tinyint not null, "
          + KEY_WINNER_PHONE + " varchar, "
          + KEY_WINNER_TICKET_NO + " char(" + TicketTable.TICKET_NO_LENGTH + "), "
          + KEY_CREATE_TIME + " datetime default current_timestamp, "
          + KEY_UPDATE_TIME + " datetime default current_timestamp, "
          + KEY_DEL_FLAG + " tinyint default 0"
          + ");";

  public static final String CREATE_TRIGGER_STATEMENT = "CREATE TRIGGER " + TRIGGER_NAME_1
          + " AFTER UPDATE ON " + TABLE_NAME
          + " BEGIN"
          + " UPDATE " + TABLE_NAME + " SET " + KEY_UPDATE_TIME + " = current_timestamp WHERE "
          + KEY_DRAW_ID + " = NEW." + KEY_DRAW_ID
          + "; END;";

  public static DrawDTO createFromCursor(Cursor c) {
    if (c == null || c.isAfterLast() || c.isBeforeFirst()) {
      return null;
    } else {
      DrawDTO d = new DrawDTO();
      d.setMDrawID(c.getInt(c.getColumnIndex(KEY_DRAW_ID)));
      d.setMRaffleID(c.getInt(c.getColumnIndex(KEY_RAFFLE_ID)));
      d.setMStatus(c.getInt(c.getColumnIndex(KEY_STATUS)));
      d.setMDrawNo(c.getString(c.getColumnIndex(KEY_DRAW_NO)));
      d.setMDrawTime(c.getLong(c.getColumnIndex(KEY_DRAW_TIME)));
      d.setMDrawMethod(c.getInt(c.getColumnIndex(KEY_DRAW_METHOD)));
      d.setMWinnerPhone(c.getString(c.getColumnIndex(KEY_WINNER_PHONE)));
      return d;
    }
  }

  public static DrawListDTO createFromListCursor(Cursor c) {
    if (c == null || c.isAfterLast() || c.isBeforeFirst()) {
      return null;
    } else {
      DrawListDTO d = new DrawListDTO();
      d.setMPrize(c.getInt(c.getColumnIndex(RaffleTable.KEY_AWARD_PRICE)));
      d.setMRaffleName(c.getString(c.getColumnIndex(RaffleTable.KEY_NAME)));
      d.setMRaffleCover(c.getString(c.getColumnIndex(RaffleTable.KEY_COVER)));
      d.setMDrawID(c.getInt(c.getColumnIndex(KEY_DRAW_ID)));
      d.setMRaffleID(c.getInt(c.getColumnIndex(KEY_RAFFLE_ID)));
      d.setMStatus(c.getInt(c.getColumnIndex(KEY_STATUS)));
      d.setMDrawNo(c.getString(c.getColumnIndex(KEY_DRAW_NO)));
      d.setMDrawTime(c.getLong(c.getColumnIndex(KEY_DRAW_TIME)));
      d.setMDrawMethod(c.getInt(c.getColumnIndex(KEY_DRAW_METHOD)));
      d.setMWinnerPhone(c.getString(c.getColumnIndex(KEY_WINNER_PHONE)));
      d.setMWinnerTicketNo(c.getString(c.getColumnIndex(KEY_WINNER_TICKET_NO)));
      return d;
    }
  }

  public static RaffleDrawDTO createRaffleDrawFromCurosr(Cursor c) {
    if (c == null || c.isAfterLast() || c.isBeforeFirst()) {
      return null;
    } else {
      RaffleDrawDTO r = new RaffleDrawDTO();
      r.setMRaffleID(c.getInt(c.getColumnIndex(RaffleTable.KEY_RAFFLE_ID)));
      r.setMName(c.getString(c.getColumnIndex(RaffleTable.KEY_NAME)));
      r.setMDesc(c.getString(c.getColumnIndex(RaffleTable.KEY_DESC)));
      r.setMOrganizer(c.getString(c.getColumnIndex(RaffleTable.KEY_ORGANIZER)));
      r.setMCover(c.getString(c.getColumnIndex(RaffleTable.KEY_COVER)));
      r.setMUnitPrice(c.getInt(c.getColumnIndex(RaffleTable.KEY_UNIT_PRICE)));
      r.setMTicketTotal(c.getInt(c.getColumnIndex(RaffleTable.KEY_TICKET_TOTAL)));
      r.setMTicketRemain(c.getInt(c.getColumnIndex(RaffleTable.KEY_TICKET_REMAIN)));
      r.setMSingleLimit(c.getInt(c.getColumnIndex(RaffleTable.KEY_SINGLE_LIMIT)));
      r.setMAwardPrice(c.getInt(c.getColumnIndex(RaffleTable.KEY_AWARD_PRICE)));
      r.setMDrawTime(c.getLong(c.getColumnIndex(RaffleTable.KEY_DRAW_TIME)));
      r.setMDrawStatus(c.getInt(c.getColumnIndex(KEY_STATUS)));
      r.setMWinnerPhone(c.getString(c.getColumnIndex(KEY_WINNER_PHONE)));
      r.setMWinnerTicketNo(c.getString(c.getColumnIndex(KEY_WINNER_TICKET_NO)));
      return r;
    }
  }

  public static void insert(SQLiteDatabase db, DrawDTO p) {
    ContentValues values = new ContentValues();
    values.put(KEY_RAFFLE_ID, p.getMRaffleID());
    values.put(KEY_DRAW_NO, p.getMDrawNo());
    values.put(KEY_STATUS, p.getMStatus());
    values.put(KEY_DRAW_TIME, p.getMDrawTime());
    values.put(KEY_DRAW_METHOD, p.getMDrawMethod());
    values.put(KEY_WINNER_PHONE, p.getMWinnerPhone());
    values.put(KEY_WINNER_TICKET_NO, p.getMWinnerTicketNo());
    db.insert(TABLE_NAME, null, values);
  }

  public static ArrayList<DrawListDTO> selectAll(SQLiteDatabase db) {
    ArrayList<DrawListDTO> results = new ArrayList<DrawListDTO>();
    String execQuery = "SELECT d.*, r." + RaffleTable.KEY_NAME + ", r." + RaffleTable.KEY_AWARD_PRICE + ", r." + RaffleTable.KEY_COVER
            + " from " + TABLE_NAME + " d LEFT OUTER JOIN " + RaffleTable.TABLE_NAME
            + " r ON d." + KEY_RAFFLE_ID + " = r." + RaffleTable.KEY_RAFFLE_ID + " WHERE d."
            + KEY_DEL_FLAG + " = ?;";
    Cursor c = db.rawQuery(execQuery, new String[]{"0"});
    if (c != null) {
      c.moveToFirst();
      while (!c.isAfterLast()) {
        results.add(createFromListCursor(c));
        c.moveToNext();
      }
    }
    return results;
  }

  public static RaffleDrawDTO joinDrawQueryByRaffleID(SQLiteDatabase db, long id) {
    RaffleDrawDTO result = null;
    String execQuery = "SELECT r.*, d." + KEY_STATUS + ", d." + KEY_WINNER_PHONE + ", d." + KEY_WINNER_TICKET_NO
            + " from " + RaffleTable.TABLE_NAME + " r LEFT OUTER JOIN " + TABLE_NAME
            + " d ON r." + RaffleTable.KEY_RAFFLE_ID + " = d." + KEY_RAFFLE_ID + " WHERE r."
            + RaffleTable.KEY_RAFFLE_ID + " = ? AND r."
            + RaffleTable.KEY_DEL_FLAG + " = ? LIMIT 1;";
    Cursor c = db.rawQuery(execQuery, new String[]{"" + id, "0"});
    if (c != null) {
      c.moveToFirst();
      result = createRaffleDrawFromCurosr(c);
    }
    return result;
  }

  public static DrawDTO getByRaffID(SQLiteDatabase db, long id) {
    Cursor c = db.query(TABLE_NAME, null, KEY_RAFFLE_ID + " = ? and " + KEY_DEL_FLAG + " = ?", new String[]{"" + id, "0"}, null, null, KEY_CREATE_TIME);
    c.moveToFirst();
    return createFromCursor(c);
  }

  public static void update(SQLiteDatabase db, DrawDTO d) {
    ContentValues values = new ContentValues();
    values.put(KEY_STATUS, d.getMStatus());
    values.put(KEY_WINNER_PHONE, d.getMWinnerPhone());
    values.put(KEY_WINNER_TICKET_NO, d.getMWinnerTicketNo());
    db.update(TABLE_NAME, values, KEY_DRAW_ID + "= ? and " + KEY_DEL_FLAG + " = ?", new String[]{"" + d.getMDrawID(), "0"});
  }

  public static void delete(SQLiteDatabase db, RaffleDTO r) {
    ContentValues values = new ContentValues();
    values.put(KEY_DEL_FLAG, 1);
    db.update(TABLE_NAME, values, KEY_RAFFLE_ID + "= ?", new String[]{"" + r.getMRaffleID()});
  }

  public static void delete(SQLiteDatabase db, DrawDTO d) {
    ContentValues values = new ContentValues();
    values.put(KEY_DEL_FLAG, 1);
    db.update(TABLE_NAME, values, KEY_DRAW_ID + "= ?", new String[]{"" + d.getMDrawID()});
  }
}
